package com.itheima.service;

import cn.afterturn.easypoi.csv.CsvExportUtil;
import cn.afterturn.easypoi.csv.entity.CsvExportParams;
import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.word.WordExportUtil;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.itheima.mapper.ResourceMapper;
import com.itheima.mapper.UserMapper;
import com.itheima.pojo.Resource;
import com.itheima.pojo.User;
//import jxl.Workbook;
import com.itheima.utils.EntityUtils;
import com.itheima.utils.ExcelExportEngine;
import com.opencsv.CSVWriter;
import com.zaxxer.hikari.HikariDataSource;
import net.sf.jasperreports.engine.JREmptyDataSource;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
//import jxl.Workbook;
//import jxl.write.Label;
//import jxl.write.WritableSheet;
//import jxl.write.WritableWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtils;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.StandardChartTheme;
import org.jfree.data.category.DefaultCategoryDataset;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import tk.mybatis.mapper.entity.Example;


import javax.imageio.ImageIO;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

import static org.apache.poi.ss.formula.functions.TextFunction.CONCATENATE;

@Service
public class UserService {
    //日期格式化
    private SimpleDateFormat simpleDateFormat = new  SimpleDateFormat("yyyy-MM-dd");


    @Autowired
    private UserMapper userMapper;

    @Autowired
    private ResourceMapper resourceMapper;

    @Autowired
    private HikariDataSource hikariDataSource;

    //根据id查询用户，并且用户中附带办公用品数据
    public User findById(Long id) {
        User user = userMapper.selectByPrimaryKey(id);
        Resource resource = new Resource();
        resource.setUserId(user.getId());
        List<Resource> resourceList = resourceMapper.select(resource);
        user.setResourceList(resourceList);
        return user;
    }

    public List<User> findAll() {
        return userMapper.selectAll();
    }

    public List<User> findPage(Integer page, Integer pageSize) {
        PageHelper.startPage(page,pageSize);  //开启分页
        Page<User> userPage = (Page<User>) userMapper.selectAll(); //实现查询
        return userPage.getResult();
    }
    /**
     * 使用jxl导出excel
     *  //编号，姓名，手机号，入职日期，现住址
     * @param response
     */
    /*public void downLoadXlsByJxl(HttpServletResponse response) throws Exception {
        ServletOutputStream outputStream = response.getOutputStream();
        //1.创建可写入的工作簿,一个excel文件
        WritableWorkbook workbook = Workbook.createWorkbook(outputStream);
        //2.创建工作表
        WritableSheet sheet = workbook.createSheet("工作簿1", 0);
        //调整列宽--var1 索引值, var2 a*256-a代表一个标准字母的宽度
        sheet.setColumnView(4,5);
        //处理标题  public Label(int c列, int r行, String cont内容)
        Label label = null;
        String[] title = new String[]{"编号","姓名","手机号","入职日期","现住址"};
        for (int i = 0; i <title.length ; i++) {
            label = new Label(i, 0, title[i]);
            sheet.addCell(label);
        }
        //处理内容-动态获取数据之后，存入excel
        int rowIndex = 1;
        List<User> users = userMapper.selectAll();
        for (User user : users) {
            Label label1 = new Label(0,rowIndex, user.getId().toString());
            sheet.addCell(label1);
            Label label2 = new Label(1,rowIndex, user.getUserName());
            sheet.addCell(label2);
            Label label3 = new Label(2,rowIndex, user.getPhone());
            sheet.addCell(label3);
            Label label4 = new Label(3,rowIndex, simpleDateFormat.format(user.getBirthday()));
            sheet.addCell(label4);
            Label label5 = new Label(4,rowIndex, user.getAddress());
            sheet.addCell(label5);
            rowIndex++;
        }
        //文件导出-下载-一个流两个头
        String filename = "一个JXL入门.xls";
        // 文件的打开方式(in-line在浏览器中直接打开,attachment附件下载)
        // 文件的下载时mime类型-excel-application/vnd.ms-excel)
        //设置文件的打开方式和mime类型，中文转码-设置为byte字节
        response.setHeader( "Content-Disposition", "attachment;filename="  + new String(filename.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.ms-excel");

        //导出
        workbook.write();
        //资源关闭
        workbook.close();
        outputStream.close();
    }*/

    /**
     *  XSSFWorkbook上传excel
     * @param file
     */
    public void uploadExcel(MultipartFile file) throws Exception {

        SimpleDateFormat simpleDateFormat = new  SimpleDateFormat("yyyy-MM-dd");

        //有内容的workbook
        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        //获取到第一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        //读取工作表中的内容
        //获取当前sheet的最后一行的索引值
        int lastRowIndex = sheet.getLastRowNum();
        Row row = null;
        //读取工作表中的内容
        for (int i = 1; i <= lastRowIndex; i++) {
            row = sheet.getRow(i);
            String username = row.getCell(0).getStringCellValue();
            String phone = null;
            try {
                phone = row.getCell(1).getStringCellValue()+"";
            } catch (Exception e) {
                e.printStackTrace();
            }
            String province = row.getCell(2).getStringCellValue();
            String city = row.getCell(3).getStringCellValue();
            Integer salary = ((Double) row.getCell(4).getNumericCellValue()).intValue();
            Date hireDate = simpleDateFormat.parse(row.getCell(5).getStringCellValue());
            Date birthDay = simpleDateFormat.parse(row.getCell(6).getStringCellValue());
            String address = row.getCell(7).getStringCellValue();
            User user = new User();
            user.setUserName(username);
            user.setBirthday(birthDay);
            user.setAddress(address);
            user.setCity(city);
            user.setHireDate(hireDate);
            user.setProvince(province);
            user.setSalary(salary);
            user.setPhone(phone);
            //执行插入方法
            //System.out.println(user.toString());
            userMapper.insert(user);
        }

    }

    /**
     * POI导出用户数据
     * @param response
     * @throws Exception
     */
    public void downLoadXlsxByPoi(HttpServletResponse response) throws Exception{
        //1.创建一个全新的工作簿
        Workbook workbook = new XSSFWorkbook();
        //2.创建全新的工作表
        Sheet sheet = workbook.createSheet("用户数据");
        //调整列宽var1索引值, var2 1代表一个标准字母的1/256
        sheet.setColumnWidth(0,5*256);
        sheet.setColumnWidth(1,8*256);
        sheet.setColumnWidth(2,10*256);
        sheet.setColumnWidth(3,15*256);
        sheet.setColumnWidth(4,30*256);
        //3.处理固定的标题---编号，姓名，手机号，入职日期，现住址
        String[] title = new String[]{"编号","姓名","手机号","入职日期","现住址"};
        //标题行
        Row titleRow = sheet.createRow(0);
        Cell cell = null;
        for (int i = 0; i < 5; i++) {
            cell = titleRow.createCell(i);
            cell.setCellValue(title[i]);
        }
        //4.从第二行循环遍历，向单元格中放入数据
        List<User> list = userMapper.selectAll();
        int rowIndex = 1;
        Row row = null;
        for (User user : list) {
            row = sheet.createRow(rowIndex);
            cell = row.createCell(0);
            cell.setCellValue(user.getId());
            cell = row.createCell(1);
            cell.setCellValue(user.getUserName());
            cell = row.createCell(2);
            cell.setCellValue(user.getPhone());
            cell = row.createCell(3);
            cell.setCellValue(simpleDateFormat.format(user.getHireDate()));
            cell = row.createCell(4);
            cell.setCellValue(user.getBirthday());
            rowIndex++;
        }
        //一个流两个头
        String fileName = "员工数据.xlsx";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.ms-excel");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //执行
        workbook.write(response.getOutputStream());
    }
    /**
     * POI导出用户数据---简单样式
     * @param response
     * @throws Exception
     */
    public void downLoadXlsxByPoiWithCellStyle(HttpServletResponse response) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        /**
         * 大标题行
         */
        Row bigTitleRow = sheet.createRow(0);

        //1.边框线(全边框)，行高(42)，合并单元格(第一行第一个到第五个单元格)，对齐方式(水平垂直居中)，字体：黑体18号
        CellStyle bigTitleRowCellStyle = workbook.createCellStyle();

        //上下左右边框
        bigTitleRowCellStyle.setBorderTop(BorderStyle.THIN);//BorderStyle.THIN细线
        bigTitleRowCellStyle.setBorderBottom(BorderStyle.THIN);
        bigTitleRowCellStyle.setBorderLeft(BorderStyle.THIN);
        bigTitleRowCellStyle.setBorderRight(BorderStyle.THIN);
        //对齐方式，水平居中对齐，垂直居中对齐
        bigTitleRowCellStyle.setAlignment(HorizontalAlignment.CENTER);
        bigTitleRowCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置行高
        bigTitleRow.setHeightInPoints(42);

        //设置列宽
        sheet.setColumnWidth(0,5*256);
        sheet.setColumnWidth(1,10*256);
        sheet.setColumnWidth(2,10*256);
        sheet.setColumnWidth(3,10*256);
        sheet.setColumnWidth(4,30*256);

        //字体
        //1.创建字体
        Font font = workbook.createFont();
        //2.设置字体样式
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 18);
        //3.字体放入样式中
        bigTitleRowCellStyle.setFont(font);

        //--5个单元格合并
        for (int i = 0; i < 5; i++) {
            Cell cell = bigTitleRow.createCell(i);
            cell.setCellStyle(bigTitleRowCellStyle);
        }
        //起始行，结束行，开始列，结束列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,4));

        //向单元格中添加句子
        bigTitleRow.getCell(0).setCellValue("用户信息数据");

        /**
         * 小标题行
         */
        Row littleTitleRow = sheet.createRow(1);

        //1.边框线(全边框)，行高(42)，合并单元格(第一行第一个到第五个单元格)，对齐方式(水平垂直居中)，字体：黑体18号
        CellStyle littleTitleRowCellStyle = workbook.createCellStyle();

        //克隆大标题的样式
        //上下左右边框
        //对齐方式，水平居中对齐，垂直居中对齐
        littleTitleRowCellStyle.cloneStyleFrom(bigTitleRowCellStyle);

        //设置列宽
        littleTitleRow.setHeightInPoints(32);
        //字体
        //1.创建字体
        Font littleFont = workbook.createFont();
        //2.设置字体样式
        littleFont.setFontName("宋体");
        littleFont.setFontHeightInPoints((short) 12);
        littleFont.setBold(true);
        //3.字体放入样式中
        littleTitleRowCellStyle.setFont(littleFont);

        //添加小标题内容
        String[] titles = new String[]{"编号","姓名","手机号","入职日期","现住址"};
        for (int i = 0; i < 5; i++) {
            Cell cell = littleTitleRow.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(littleTitleRowCellStyle);
        }

        /**
         * 内容样式
         */
        CellStyle contentRowCellStyle = workbook.createCellStyle();
        contentRowCellStyle.cloneStyleFrom(bigTitleRowCellStyle);
        contentRowCellStyle.setAlignment(HorizontalAlignment.LEFT);
        Font contentFont = workbook.createFont();
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short) 11);
        contentFont.setBold(false);
        contentRowCellStyle.setFont(contentFont);

        Row contentRow = null;
        Cell cell = null;
        int contentRowIndex = 2;
        //添加用户数据
        List<User> users = userMapper.selectAll();
        for (User user : users) {
            contentRow = sheet.createRow(contentRowIndex);

            cell = contentRow.createCell(0);
            cell.setCellValue(user.getId());

            cell = contentRow.createCell(1);
            cell.setCellValue(user.getUserName());

            cell = contentRow.createCell(2);
            cell.setCellValue(user.getPhone());

            cell = contentRow.createCell(3);
            cell.setCellValue(simpleDateFormat.format(user.getHireDate()));

            cell = contentRow.createCell(4);
            cell.setCellValue(user.getAddress());

            cell.setCellStyle(contentRowCellStyle);
            contentRowIndex++;
        }
        /**
         * 一个流两个头
         */
        String fileName = "员工样式数据.xlsx";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.ms-excel");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(response.getOutputStream());
    }
    /**
     * POI导出用户数据---简单样式--批量
     * @param response
     * @throws Exception
     */
    public void downLoadXlsxByPoiWithTemplate(HttpServletResponse response) throws Exception {
        //1.获取到模板
        //获取项目的根目录，创建目录
        File rootFile = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootFile, "/excel_template/userList.xlsx");
        //创建有模板的工作簿
        Workbook workbook = new XSSFWorkbook(templateFile);
        Sheet sheet = workbook.getSheetAt(0);
        //2.查询所有的用户数据
        List<User> list = userMapper.selectAll();
        //3.放入到模板中
        int rowIndex = 2;
        Row row = null;
        Cell cell = null;
        //获取准备好的内容单元格样式，第二个sheet的第一个单元格的样式
        CellStyle cellStyle = workbook.getSheetAt(1).getRow(0).getCell(0).getCellStyle();

        for (User user : list) {
            row = sheet.createRow(rowIndex);
            row.setHeightInPoints(15);
            cell = row.createCell(0);
            cell.setCellValue(user.getId());
            cell = row.createCell(1);
            cell.setCellValue(user.getUserName());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(2);
            cell.setCellValue(user.getPhone());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(3);
            cell.setCellValue(simpleDateFormat.format(user.getHireDate()));
            cell.setCellStyle(cellStyle);
            cell = row.createCell(4);
            cell.setCellValue(user.getAddress());
            cell.setCellStyle(cellStyle);
            rowIndex++;
        }
        //删除第二个sheet
        workbook.removeSheetAt(1);
        //4.导出文件
        //一个流两个头
        String fileName = "员工模板数据.xlsx";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.ms-excel");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //执行
        workbook.write(response.getOutputStream());
    }

    /**
     * 导出用户详细信息--使用模板
     * @param id
     * @param response
     * @throws Exception
     */
    public void downLoadUserInfoByTemplate(Long id, HttpServletResponse response) throws Exception {
        //1.读取模板
        //获取项目的根目录，创建目录---获取绝对路径
        File rootPath = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootPath.getAbsolutePath(), "/excel_template/userInfo.xlsx");
        //创建有模板的工作簿
        Workbook workbook = new XSSFWorkbook(templateFile);
        Sheet sheet = workbook.getSheetAt(0);
        //2.获取用户数据
        User user = userMapper.selectByPrimaryKey(id);
        //3.数据放入模板---存入哪一行哪个单元格
        //用户名 第2行第2列
        sheet.getRow(1).getCell(1).setCellValue(user.getUserName());
        //手机号 第3行第2列
        sheet.getRow(2).getCell(1).setCellValue(user.getPhone());
        //生日 第4行第2列
        sheet.getRow(3).getCell(1).setCellValue(simpleDateFormat.format(user.getBirthday()));
        //工资 第5行第2列
        sheet.getRow(4).getCell(1).setCellValue(user.getSalary());
        //入职日期 第6行第2列
        sheet.getRow(5).getCell(1).setCellValue(simpleDateFormat.format(user.getHireDate()));
        //省份 第7行第2列
        sheet.getRow(6).getCell(1).setCellValue(user.getProvince());
        //城市 第7行第4列
        sheet.getRow(6).getCell(3).setCellValue(user.getCity());
        //现住址 第8行第1列---合并单元格
        sheet.getRow(7).getCell(1).setCellValue(user.getAddress());
        //司龄 第6行第4列---导出公式(入职时间到现在) 使用公式稍后处理 =CONCATENATE(DATEDIF(B6,TODAY(),"Y"),"年",DATEDIF(B6,TODAY(),"YM"),"个月")
        sheet.getRow(5).getCell(3).setCellFormula("CONCATENATE(DATEDIF(B6,TODAY(),\"Y\"),\"年\",DATEDIF(B6,TODAY(),\"YM\"),\"个月\")");
        //照片--第二行到第五行，第三列到第四列--合并单元格
        //先创建一个字节输出流
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        //读取图片，放入一个带有缓冲区的图片类中，主要作用是将一副图片加载到内存中
        //优化，获取拓展名
        String userPhoto = user.getPhoto();
        String extName = userPhoto.substring(userPhoto.lastIndexOf(".") + 1).toUpperCase();
        BufferedImage bufferedImage = ImageIO.read(new File(rootPath+user.getPhoto()));
        //把图片写入到字节输出流中
        ImageIO.write(bufferedImage,extName,outputStream);
        //Patriarch图片写入
        Drawing patriarch = sheet.createDrawingPatriarch();
        //ClientAnchor指定图片位置--后四起始列，起始行，结束列，结束行,前四个参数，左上角向x,y偏离多少，右下角向x,y偏离多少(偏移单位-1cm=36w)
        ClientAnchor clientAnchor = new XSSFClientAnchor(0, 0, 0, 0, 2, 1, 4, 5);
        //优化
        int format = 0;
        switch (extName){
            case "JPG":{
                format = XSSFWorkbook.PICTURE_TYPE_JPEG;
            } case "JPEG":{
                format = XSSFWorkbook.PICTURE_TYPE_JPEG;
            } case "PNG":{
                format = XSSFWorkbook.PICTURE_TYPE_PNG;
            }
        }

        //开始把图片写入到sheet指定的位置
        patriarch.createPicture(clientAnchor,workbook.addPicture(outputStream.toByteArray(),format));
        //图片处理结束

        //4.下载
        //一个流两个头
        String fileName = "员工("+user.getUserName()+")详细数据.xlsx";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.ms-excel");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //执行
        workbook.write(response.getOutputStream());
    }
    /**
     * 导出用户详细信息--使用模板引擎
     * @param id
     * @param response
     * @throws Exception
     */
    public void downLoadUserInfoByTemplate2(Long id, HttpServletResponse response) throws Exception {
        //1.读取模板
        //获取项目的根目录，创建目录---获取绝对路径
        File rootPath = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootPath.getAbsolutePath(), "/excel_template/userInfo2.xlsx");
        //创建有模板的工作簿
        Workbook workbook = new XSSFWorkbook(templateFile);
        Sheet sheet = workbook.getSheetAt(0);
        //2.获取用户数据
        User user = userMapper.selectByPrimaryKey(id);
        System.out.println("获取到的用户信息"+user.toString());
        //3.通过自定义引擎放入数据--图片的绝对路径
        workbook = ExcelExportEngine.writeToExcel(user, workbook,rootPath.getPath()+user.getPhoto());

        //System.out.println("路径1"+rootPath.getPath());
        //System.out.println("路径2"+rootPath.getAbsolutePath());
        //System.out.println("路径3"+rootPath.getCanonicalPath());

//
//        //照片--第二行到第五行，第三列到第四列--合并单元格
//        //先创建一个字节输出流
//        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
//        //读取图片，放入一个带有缓冲区的图片类中，主要作用是将一副图片加载到内存中
//        //优化，获取拓展名
//        String userPhoto = user.getPhoto();
//        String extName = userPhoto.substring(userPhoto.lastIndexOf(".") + 1).toUpperCase();
//        BufferedImage bufferedImage = ImageIO.read(new File(rootPath+user.getPhoto()));
//        //把图片写入到字节输出流中
//        ImageIO.write(bufferedImage,extName,outputStream);
//        //Patriarch图片写入
//        Drawing patriarch = sheet.createDrawingPatriarch();
//        //ClientAnchor指定图片位置--后四起始列，起始行，结束列，结束行,前四个参数，左上角向x,y偏离多少，右下角向x,y偏离多少(偏移单位-1cm=36w)
//        ClientAnchor clientAnchor = new XSSFClientAnchor(0, 0, 0, 0, 2, 1, 4, 5);
//        //优化
//        int format = 0;
//        switch (extName){
//            case "JPG":{
//                format = XSSFWorkbook.PICTURE_TYPE_JPEG;
//            } case "JPEG":{
//                format = XSSFWorkbook.PICTURE_TYPE_JPEG;
//            } case "PNG":{
//                format = XSSFWorkbook.PICTURE_TYPE_PNG;
//            }
//        }
//
//        //开始把图片写入到sheet指定的位置
//        patriarch.createPicture(clientAnchor,workbook.addPicture(outputStream.toByteArray(),format));
        //图片处理结束

        //4.下载
        //一个流两个头
        String fileName = "员工("+user.getUserName()+")详细数据.xlsx";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        //xls
        //response.setContentType("application/vnd.ms-excel");
        //xlsx
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //执行
        workbook.write(response.getOutputStream());
    }

    /**
     * 百万数据导出--高版本Excel
     * 使用sax方式解析Excel--XML
     * 不能使用模板，不能使用太多样式
     * @param response
     */
    public void downLoadMillion(HttpServletResponse response) throws Exception {
        //使用sax方式解析
        Workbook workbook = new SXSSFWorkbook();
        //导出500w条数据，一个sheet只能放100w行

        //记录了处理了多少条数据
        int number = 0;
        //分页查询
        int page = 1;
        //记录row的行索引
        int rowIndex = 1;
        //创建行
        Row row = null;
        Sheet sheet = null;
        while (true) {
            List<User> userList = this.findPage(page, 1000000);
            if (CollectionUtils.isEmpty(userList)) {
                break;//数据为空不再查询
            }

            if (number % 1000000 == 0) {
                //表示应该创建新的标题
                 sheet = workbook.createSheet("第" + (number/1000000)+ "个sheet表");
                //重置rowIndex
                rowIndex = 1;

                //设置列宽
                sheet.setColumnWidth(0,8*256);
                sheet.setColumnWidth(1,12*256);
                sheet.setColumnWidth(2,15*256);
                sheet.setColumnWidth(3,15*256);
                sheet.setColumnWidth(4,30*256);

                //设置小标题
                String[] title = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
                //创建标题行
                Row titleRow = sheet.createRow(0);
                for (int i = 0; i < 5; i++) {
                    titleRow.createCell(i).setCellValue(title[i]);
                }
            }
            //向excel中添加内容

            for (User user : userList) {
                row = sheet.createRow(rowIndex);
                row.createCell(0).setCellValue(user.getId());
                row.createCell(1).setCellValue(user.getUserName());
                row.createCell(2).setCellValue(user.getPhone());
                row.createCell(3).setCellValue(simpleDateFormat.format(user.getHireDate()));
                row.createCell(4).setCellValue(user.getAddress());

                rowIndex++;
                number++;
            }
            page++;//下一页

        }

        //4.下载
        //一个流两个头
        String fileName = "百万数据导出.xlsx";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //执行
        workbook.write(response.getOutputStream());
    }

    /**
     * 导出百万数据到CSV文件
     * @param response
     * @throws Exception
     */
    public void downLoadCSV(HttpServletResponse response) throws Exception {
        ServletOutputStream outputStream = response.getOutputStream();

        //导出
        String fileName = "csv百万数据导出.csv";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("text/csv");
        //写文件
        CSVWriter csvWriter = new CSVWriter(new OutputStreamWriter(outputStream,"UTF-8"));
        //写第一行
        //设置小标题
        String[] title = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
        csvWriter.writeNext(title);
        int page = 1;
        while (true) {
            List<User> userList = this.findPage(1, 2000000);
         if(CollectionUtils.isEmpty(userList)){
             break;
         }for(User user : userList){
             csvWriter.writeNext(new String[]{user.getId().toString(),user.getUserName(),user.getPhone(),simpleDateFormat.format(user.getHireDate()),user.getAddress()});
            }
         page++;
         csvWriter.flush();
        }
        csvWriter.close();
    }


    /**
     * 下载用户合同
     * @param id
     */
    public void downloadContract(Long id,HttpServletResponse response) throws Exception {
        //1.读取到模板
        //获取项目的根目录，创建目录---获取绝对路径
        File rootPath = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootPath.getAbsolutePath(), "word_template/contract_template.docx");
        XWPFDocument document = new XWPFDocument(new FileInputStream(templateFile));
        //2.查询当前用户--->map方便操作
        User user = this.findById(id);
        HashMap<String, String> hashMap = new HashMap<>();
        hashMap.put("userName",user.getUserName());
        hashMap.put("hireDate",simpleDateFormat.format(user.getHireDate()));
        hashMap.put("address",user.getAddress());
        //3.1.替换数据--处理正文
        List<XWPFParagraph> paragraphs = document.getParagraphs();
        for (XWPFParagraph paragraph : paragraphs) {
            List<XWPFRun> runs = paragraph.getRuns();
            for (XWPFRun run : runs) {
                String text = run.getText(0);
                //循环遍历是否包含map的key
                for (String key : hashMap.keySet()) {
                    if(text.contains(key)){
                        //如果包含，替换掉
                        run.setText(text.replaceAll(key,hashMap.get(key)),0);
                    }
                }
            }
        }
        //3.2.表格数据
        List<Resource> resources = user.getResourceList();
        //获取表格
        XWPFTable table = document.getTables().get(0);
        //获取第一行
        XWPFTableRow row = table.getRow(0);
        //存放东西，向表格中添加行
        int rowIndex = 1;
        for (Resource resource : resources) {
            //添加行
            //table.addRow(row);
            copyRow(table,row,rowIndex);
            XWPFTableRow tableRow = table.getRow(rowIndex);
            tableRow.getCell(0).setText(resource.getName());
            tableRow.getCell(1).setText(resource.getPrice().toString());
            tableRow.getCell(2).setText(resource.getNeedReturn()?"需要":"不需要");
            //照片处理
            File imageFile = new File(rootPath, "/static"+resource.getPhoto());
            setCellImage(tableRow.getCell(3),imageFile);


            rowIndex++;
        }


        //4.导出word
        String fileName = "员工"+user.getUserName()+"合同.docx";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.wordprocessingml.document");
        document.write(response.getOutputStream());
    }

    /**
     * 向单元格写入图片
     */
    private void setCellImage(XWPFTableCell cell, File imageFile) {
        XWPFRun run = cell.getParagraphs().get(0).createRun();
        //流会自动关闭
        try(FileInputStream inputStream = new FileInputStream(imageFile)) {
            //输入流，图片类型，文件名，高度，宽度(图片扩大倍数)
            run.addPicture(inputStream,XWPFDocument.PICTURE_TYPE_JPEG,imageFile.getName(), Units.toEMU(100), Units.toEMU(50));
        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    /**
     * 用户深复制行
     */
    private void copyRow(XWPFTable table, XWPFTableRow sourceRow, int rowIndex) {
        XWPFTableRow targetRow = table.insertNewTableRow(rowIndex);
        //设置行属性
        targetRow.getCtRow().setTrPr(sourceRow.getCtRow().getTrPr());
        //获取源行的单元格--设置单元格属性
        List<XWPFTableCell> cells = sourceRow.getTableCells();
        //
        if(CollectionUtils.isEmpty(cells)){
            return;
        }
          XWPFTableCell targetCell = null;
            for (XWPFTableCell cell : cells) {
                targetCell = targetRow.addNewTableCell();
                //附上单元格的样式
                //单元格的样式
                targetCell.getCTTc().setTcPr(cell.getCTTc().getTcPr());
                //单元个中段落属性
                targetCell.getParagraphs().get(0).getCTP().setPPr(cell.getParagraphs().get(0).getCTP().getPPr());
            }
        }

    /**
     * 使用EasyPOI方式导出
     * @param response
     */
    public void downLoadWithEasyPOI(HttpServletResponse response) throws Exception {
        //文件名，sheet名，文件类型
        ExportParams exportParams = new ExportParams("员工信息列表","数据", ExcelType.XSSF);
        List<User> userList = userMapper.selectAll();

        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, userList);

        //4.下载
        //一个流两个头
        String fileName = "POI数据导出.xlsx";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //执行
        workbook.write(response.getOutputStream());
    }

    /**
     * 使用easyPOI方式导入数据
     */
    public void uploadExcelWithEasyPOI(MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        importParams.setNeedSave(false);//是否需要保存
        importParams.setTitleRows(1);//大标题占多少行
        importParams.setHeadRows(1);//小标题占多少行
        //输入流，指定类型
        List<User> userList = ExcelImportUtil.importExcel(file.getInputStream(),User.class,importParams);
        for (User user : userList) {
            //数据库中已有id，新增主键报错
            user.setId(null);
            userMapper.insert(user);
        }

    }

    /**
     * 使用EasyPOI，根据模板导出数据
     * @param id
     * @param response
     */
    public void downLoadUserInfoByTemplateEasyPOI(Long id, HttpServletResponse response) throws Exception {
        //1.读取模板
        //获取项目的根目录，创建目录---获取绝对路径
        File rootPath = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootPath.getAbsolutePath(), "/excel_template/userInfo3.xlsx");
        //用来做导出的param,模板地址，是否遍历sheet
        TemplateExportParams exportParams = new TemplateExportParams(templateFile.getPath(),true);
        User user = userMapper.selectByPrimaryKey(id);
        //处理图片
        ImageEntity imageEntity = new ImageEntity();
        //图片路径
        imageEntity.setUrl(rootPath+user.getPhoto());
        //设置图片-铺满--占几行，占几列
        imageEntity.setColspan(2);
        imageEntity.setRowspan(4);
//        imageEntity.setWidth(50);
//        imageEntity.setHeight(100);
        Map<String, Object> map = EntityUtils.entityToMap(user);
        map.put("photo",imageEntity);
        //导出
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,map);
        //4.下载
        //一个流两个头
        String fileName = "EasyPOI用户数据导出.xlsx";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //执行
        workbook.write(response.getOutputStream());
    }

    public void downLoadCSVByEasyPOI(HttpServletResponse response) throws Exception {
        ServletOutputStream outputStream = response.getOutputStream();

        //导出
        String fileName = "csv数据导出.csv";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("text/csv");


        CsvExportParams csvExportParams = new CsvExportParams();
        List<User> userList = userMapper.selectAll();
        //参数，类型，数据，输出流User.class和excel共用的
        //忽略到图片导出
        csvExportParams.setExclusions(new String[]{"照片"});
        CsvExportUtil.exportCsv(csvExportParams,User.class,userList,outputStream);
    }

    public void downloadContractByEasyPOI(Long id, HttpServletResponse response) throws Exception {
        //获取模板文档
        //获取项目的根目录，创建目录---获取绝对路径
        File rootPath = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootPath.getAbsolutePath(), "word_template/contract_template2.docx");
        //准备数据
        User user = this.findById(id);
        //数据转map
        HashMap<String, Object> hashMap = new HashMap<>();
        hashMap.put("userName",user.getUserName());
        hashMap.put("hireDate",simpleDateFormat.format(user.getHireDate()));
        hashMap.put("address",user.getAddress());

        ImageEntity imageEntity = new ImageEntity();
        imageEntity.setUrl(rootPath+user.getPhoto());
        //System.out.println("用户图片"+rootPath+user.getPhoto());
        imageEntity.setHeight(100);
        imageEntity.setWidth(50);
        hashMap.put("picture",imageEntity);
        //resourceList转map
        List<Map> resourceMapList = new ArrayList<>();
        Map<String,Object> map = null;
        for (Resource resource : user.getResourceList()) {
            map = new HashMap<>();
            map.put("name",resource.getName());
            map.put("price",resource.getPrice());
            map.put("needReturn",resource.getNeedReturn());
            //图片处理---在表格中做不到，需要POI
            ImageEntity image = new ImageEntity();
            image.setUrl(templateFile+"/static/"+resource.getPhoto());
            map.put("photo",image);
            resourceMapList.add(map);
        }
        hashMap.put("resourceList",resourceMapList);

        //模板文档结合数据--产生新的文档
        XWPFDocument document = WordExportUtil.exportWord07(templateFile.getPath(), hashMap);

        //4.导出word
        String fileName = "员工"+user.getUserName()+"合同.docx";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.wordprocessingml.document");
        document.write(response.getOutputStream());

    }

    public void downLoadPDF(HttpServletResponse response) throws Exception {
        //获取模板文件
        //获取项目的根目录，创建目录---获取绝对路径
        File rootPath = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootPath.getAbsolutePath(), "pdf_template/userList_db.jasper");
        //准备数据库连接
        HashMap<String, Object> params = new HashMap<>();
        JasperPrint jasperPrint = JasperFillManager.fillReport(new FileInputStream(templateFile), params, hikariDataSource.getConnection());
        ServletOutputStream outputStream = response.getOutputStream();
        //pdf导出
        String fileName = "用户列表数据.pdf";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/pdf");
        JasperExportManager.exportReportToPdfStream(jasperPrint,outputStream);
    }
    //    private Connection getCon() throws Exception{
//        Class.forName("com.mysql.jdbc.Driver");
//        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/report_manager_db?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false", "root", "12345678");
//        return connection;
//    }

    public void downLoadPDF2(HttpServletResponse response) throws Exception {
        //获取模板文件
        //获取项目的根目录，创建目录---获取绝对路径
        File rootPath = new File(ResourceUtils.getURL("classpath:").getPath());
        //File templateFile = new File(rootPath.getAbsolutePath(), "pdf_template/user_lilst.jasper");
        File templateFile = new File(rootPath.getAbsolutePath(), "pdf_template/userList2.jasper");
        //准备数据库连接
        HashMap<String, Object> params = new HashMap<>();
        //构建查询用户条件--找个example用来操作用户
        Example example = new Example(User.class);
        //省份进行排序之后再查询
        example.setOrderByClause("province");
        List<User> userList = userMapper.selectByExample(example);
        //给hirDateStr赋值
         userList = userList.stream().map(user -> {
            user.setHireDateStr(simpleDateFormat.format(user.getHireDate()));
            return user;
        }).collect(Collectors.toList());
        //数据存放到JRBeanCollectionDataSource中
        JRBeanCollectionDataSource dataSource = new JRBeanCollectionDataSource(userList);
        JasperPrint jasperPrint = JasperFillManager.fillReport(new FileInputStream(templateFile), params, dataSource);
        ServletOutputStream outputStream = response.getOutputStream();
        //pdf导出
        String fileName = "用户列表数据.pdf";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/pdf");
        JasperExportManager.exportReportToPdfStream(jasperPrint,outputStream);

    }

    public void downLoadUserInfoPDF(Long id, HttpServletResponse response) throws Exception {
        //获取模板文件
        //获取项目的根目录，创建目录---获取绝对路径
        File rootPath = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootPath.getAbsolutePath(), "pdf_template/userInfo.jasper");

        //处理param
        User user = userMapper.selectByPrimaryKey(id);
        Map<String, Object> params = EntityUtils.entityToMap(user);
        //数据类型转换--和模板数据保持一致
        params.put("salary",user.getSalary().toString());
        //图片处理
        params.put("photo",rootPath.getPath()+user.getPhoto());
        //不需要列表数据，不处理filed处理param
        JasperPrint jasperPrint = JasperFillManager.fillReport(new FileInputStream(templateFile), params, new JREmptyDataSource());
        ServletOutputStream outputStream = response.getOutputStream();
        //pdf导出
        String fileName = "用户列表数据.pdf";
        response.setHeader("content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/pdf");
        JasperExportManager.exportReportToPdfStream(jasperPrint,outputStream);


    }


    public void jfreeChart(HttpServletResponse response) throws Exception {

        //创建数据集
        DefaultCategoryDataset dataset = new DefaultCategoryDataset();
        //设置值，x轴，分类，y轴
        dataset.addValue(20,"开发部","2011年");
        dataset.addValue(25,"开发部","2012年");
        dataset.addValue(26,"开发部","2013年");
        dataset.addValue(28,"开发部","2014年");
        dataset.addValue(8,"销售部","2011年");
        dataset.addValue(5,"销售部","2012年");
        dataset.addValue(9,"销售部","2013年");
        dataset.addValue(10,"销售部","2014年");
        dataset.addValue(2,"人事部","2011年");
        dataset.addValue(1,"人事部","2012年");
        dataset.addValue(3,"人事部","2013年");
        dataset.addValue(1,"人事部","2014年");
        //处理中文乱码--设置主题
        StandardChartTheme cn = new StandardChartTheme("CN");
        //设置大标题字体
        cn.setExtraLargeFont(new java.awt.Font("华文宋体", java.awt.Font.BOLD,20));
        //设置图例字体
        cn.setRegularFont(new java.awt.Font("华文宋体", java.awt.Font.BOLD,15));
        //设置内容x,y轴
        cn.setLargeFont(new java.awt.Font("华文宋体", java.awt.Font.BOLD,15));
        ChartFactory.setChartTheme(cn);
        //大标题，X轴说明，Y轴说明，数据集
        JFreeChart chart = ChartFactory.createBarChart("公司人数", "年份", "入职人数", dataset);
        //存放位置,根据谁生成，宽，高
        ChartUtils.writeChartAsJPEG(response.getOutputStream(),chart,400,300);

    }
}


